package com.amarsoft.apot.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellValue;
import org.joda.time.DateTime;
import java.io.FileInputStream;
import java.io.IOException;

import static org.apache.poi.ss.usermodel.CellType.*;

/**
 * 读取Excel工作簿
 */
public class ReadExcelUtils {

    /**
     * @param path 读取的Excel的路径
     * @param sheetName  sheet工作表名称
     * @throws IOException
     */
    public static void readExcel(String path, String sheetName) throws IOException {
        //创建输入流
        FileInputStream fileInputStream = new FileInputStream(path);
        //获得poi输入流
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fileInputStream);
        //获得文档
        HSSFWorkbook Workbook = new HSSFWorkbook(poifsFileSystem);
        //根据name获取sheet表
        HSSFSheet sheet = Workbook.getSheet(sheetName);
        // Workbook.getNumberOfSheets() =>获取这个工作簿的所有sheet个数

        //获得行数，下标从0开始
        int lastRow = sheet.getLastRowNum();// 或者sheet.getPhysicalNumberOfRows()
        System.out.println("行数：" + (lastRow + 1));
        //获取第二行（第一行一般是标题）
        HSSFRow row = sheet.getRow(1);
        //获得列数，下标从1开始
        int lastCell = row.getLastCellNum();// 或者row.getPhysicalNumberOfCells()
        System.out.println("列数：" + lastCell);

        for (int i = 0; i <= lastRow; i++) { //遍历每一行
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < lastCell; j++) {
                    //遍历每一列的cell
                    HSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        String value = "";
                        switch (cell.getCellType()) { //SwitchCase判断单元格的类型
                            case STRING: // 字符串类型
                                value = cell.getStringCellValue();
                                break;

                            case BOOLEAN: // Boolean类型
                                value = String.valueOf(cell.getBooleanCellValue());
                                break;

                            case NUMERIC: // 数值(日期,普通数字)
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
                                    value = new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss");
                                } else { // 普通数字
                                    cell.setCellType(STRING);// 防止数字过长转成String
                                    value = cell.toString();
                                }
                                break;

                            case FORMULA: // 计算公式
                                String cellFormula = cell.getCellFormula(); //获取计算公式
                                System.out.println(cellFormula);
                                // 根据计算公式计算
                                CellValue evaluate = Workbook.getCreationHelper().createFormulaEvaluator().evaluate(cell);
                                value = evaluate.formatAsString();
                                break;

                            case BLANK: // 空值
                                value = "";
                                break;

                            case ERROR: // 类型错误
                                value = "类型错误";
                                break;

                            default:
                                value = "未知类型";
                                break;
                        }

                        System.out.print(value + " ");
                    }
                }
                System.out.println();
            }
        }
        fileInputStream.close();
    }

}
